﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

using System.Data.SQLite;

namespace APX
{
    public partial class w_Main : Form
    {
        static SQLiteConnection database;
        static DataSet data;
        static List<TextBox> textBoxes;
        static List<ComboBox> comboBoxes;
        static List<RadioButton> radioButtons;
        static SQLiteDataAdapter current;

        public w_Main()
        {
            InitializeComponent();

            textBoxes = new List<TextBox>();
            comboBoxes = new List<ComboBox>();
            radioButtons = new List<RadioButton>();

            foreach (Control x in tab_Account.Controls)
            {
               if (x is TextBox)
                  textBoxes.Add( (TextBox)x );
               else if (x is ComboBox)
                  comboBoxes.Add( (ComboBox)x );
            }
            foreach (Control x in tab_Customer.Controls)
            {
               if (x is TextBox)
                  textBoxes.Add((TextBox)x);
               else if (x is ComboBox)
                  comboBoxes.Add( (ComboBox)x );
            }
            foreach (Control x in tab_Equipment.Controls)
            {
               if (x is GroupBox)
                  foreach (Control y in x.Controls)
                  {
                     if (y is TextBox)
                        textBoxes.Add((TextBox)y);
                     else if (y is ComboBox)
                        comboBoxes.Add((ComboBox)y);
                     else if (y is RadioButton)
                        radioButtons.Add( (RadioButton)y );
                  }
            }

            cbb_Rep.ReadOnly = true;

           data = new DataSet("APX");

           data.ReadXml("..//..//states.xml");
           cbb_State.DataSource = data;
           cbb_State.DisplayMember = "state.abbreviation";
           cbb_State.ValueMember = "state.name";

            cbb_SearchBy.SelectedIndex = 0;
        }

        private void SetUp(String db)
        {
           try
           {
              SQLiteDataAdapter accounts, repList, officeList, callStations, city, state, zip;
              database = new SQLiteConnection("Data Source=" + db + ";Version=3;");
              database.Open();

              spl_Find_Display.Visible = true;

              callStations = new SQLiteDataAdapter("SELECT * FROM CallStations;", database);
              callStations.TableMappings.Add("Table", "CallStations");
              callStations.Fill(data);

              repList = new SQLiteDataAdapter("SELECT * FROM Reps;", database);
              repList.TableMappings.Add("Table", "Reps");
              repList.Fill(data);

              officeList = new SQLiteDataAdapter("SELECT * FROM Offices;", database);
              officeList.TableMappings.Add("Table", "Offices");
              officeList.Fill(data);

              accounts = new SQLiteDataAdapter("SELECT * FROM Accounts;", database);
              accounts.TableMappings.Add("Table", "Accounts");
              accounts.Fill(data);

              city = new SQLiteDataAdapter("SELECT DISTINCT City FROM Accounts;", database);
              city.TableMappings.Add("Table", "City");
              city.Fill(data);

              state = new SQLiteDataAdapter("SELECT DISTINCT State FROM Accounts;", database);
              state.TableMappings.Add("Table", "State");
              state.Fill(data);

              zip = new SQLiteDataAdapter("SELECT DISTINCT Zip FROM Accounts;", database);
              zip.TableMappings.Add("Table", "Zip");
              zip.Fill(data);

              cbb_RepFilter.DataSource = data;
              cbb_RepFilter.DisplayMember = "Reps.Name";
              cbb_RepFilter.ValueMember = "Reps.Name";

              cbb_Rep.DataSource = data;
              cbb_Rep.DisplayMember = "Reps.Name";
              cbb_Rep.ValueMember = "Reps.Name";

              cbb_OfficeFilter.DataSource = data;
              cbb_OfficeFilter.DisplayMember = "Offices.Location";
              cbb_OfficeFilter.ValueMember = "Offices.Location";

              cbb_Office.DataSource = data;
              cbb_Office.DisplayMember = "Offices.Location";
              cbb_Office.ValueMember = "Office.Location";

              cbb_City.DataSource = data;
              cbb_City.DisplayMember = "City.City";
              cbb_City.ValueMember = "City.City";

              cbb_StateFilter.DataSource = data;
              cbb_StateFilter.DisplayMember = "State.State";
              cbb_StateFilter.ValueMember = "State.State";

              cbb_Zip.DataSource = data;
              cbb_Zip.DisplayMember = "Zip.Zip";
              cbb_Zip.ValueMember = "Zip.Zip";
           }
           catch (Exception e)
           {
              Console.WriteLine("Error: " + e);
           }
        }

        private void getAccount(String query)
        {
           current = new SQLiteDataAdapter(query, database);
           current.TableMappings.Add("Table", "Current");
           current.Fill(data);

           tx_AR.DataBindings.Add("Text", data, "Current.AR");
           tx_CSID.DataBindings.Add("Text", data, "Current.CSID");
           tx_Final.DataBindings.Add("Text", data, "Current.Final");
           tx_Comments.DataBindings.Add("Text", data, "Current.Comments");

           tx_Name.DataBindings.Add("Text", data, "Current.Customer");
           tx_Address.DataBindings.Add("Text", data, "Current.Address");
           tx_City.DataBindings.Add("Text", data, "Current.City");
           cbb_State.DataBindings.Add("SelectedValue", data, "Current.State");
           tx_Zip.DataBindings.Add("Text", data, "Current.Zip");

           tx_Points.DataBindings.Add("Text", data, "Current.Points");
           tx_Upgrades.DataBindings.Add("Text", data, "Current.Upgrades");

           tx_5816.DataBindings.Add("Text", data, "Current.DoorWindow");
           tx_5818.DataBindings.Add("Text", data, "Current.Recessed");
           tx_5853.DataBindings.Add("Text", data, "Current.Glassbreak");
           tx_5894.DataBindings.Add("Text", data, "Current.Motion");
           tx_5808.DataBindings.Add("Text", data, "Current.Smoke");
           tx_5804.DataBindings.Add("Text", data, "Current.Keyfob");
           tx_5802.DataBindings.Add("Text", data, "Current.Panic");
           tx_5878.DataBindings.Add("Text", data, "Current.Wallfob");

           switch (data.Tables["Current"].Rows[0]["Panel"].ToString())
           {
              case "APX32":
              case "Lynx":
                 rb_Lynx.Checked = true;
                 break;
              case "Vista":
              case "Vista15":
                 rb_Vista.Checked = true;
                 break;
           }

           switch (data.Tables["Current"].Rows[0]["Commo"].ToString())
           {
              case "2 Way":
                 rb_2Way.Checked = true;
                 break;
              case "Cell Primary":
                 rb_CP.Checked = true;
                 break;
              case "Cell Backup":
                 rb_CBU.Checked = true;
                 break;
           }
        }

        private void exitToolStripMenuItem_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void salesRepToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new Pop_Up_Dialogs.NewEmployee().ShowDialog();
        }

        private void officeToolStripMenuItem_Click(object sender, EventArgs e)
        {
            new Pop_Up_Dialogs.NewOffice().ShowDialog();
        }

        private void callStationToolStripMenuItem_Click(object sender, EventArgs e)
        {
           new Pop_Up_Dialogs.NewCallStation().ShowDialog();
        }

        private void loadDatabaseToolStripMenuItem_Click(object sender, EventArgs e)
        {
           dlg_LoadDatabase.ShowDialog();
        }

        private void openFileDialog1_FileOk(object sender, CancelEventArgs e)
        {
           SetUp(dlg_LoadDatabase.FileName);
        }

        private void ckb_Rep_CheckedChanged(object sender, EventArgs e)
        {
           if (ckb_Rep.CheckState == CheckState.Checked)
              cbb_RepFilter.Enabled = true;
           else cbb_RepFilter.Enabled = false;
        }

        private void ckb_Office_CheckedChanged(object sender, EventArgs e)
        {
           if (ckb_Office.CheckState == CheckState.Checked)
              cbb_OfficeFilter.Enabled = true;
           else cbb_OfficeFilter.Enabled = false;
        }

        private void ckb_City_CheckedChanged(object sender, EventArgs e)
        {
           if (ckb_City.CheckState == CheckState.Checked)
              cbb_City.Enabled = true;
           else cbb_City.Enabled = false;
        }

        private void ckb_State_CheckedChanged(object sender, EventArgs e)
        {
           if (ckb_State.CheckState == CheckState.Checked)
              cbb_StateFilter.Enabled = true;
           else cbb_StateFilter.Enabled = false;
        }

        private void ckb_Zip_CheckedChanged(object sender, EventArgs e)
        {
           if (ckb_Zip.CheckState == CheckState.Checked)
              cbb_Zip.Enabled = true;
           else cbb_Zip.Enabled = false;
        }

        private void b_Find_Click(object sender, EventArgs e)
        {
           String query = "SELECT * FROM Accounts;";
           switch (cbb_SearchBy.SelectedIndex)
           {
              case 0:
                 if (!validateAR(tx_Find.Text))
                    return;
                 query = "SELECT * FROM Accounts WHERE AR = '" + tx_Find.Text + "';";
                 break;
              case 1:
                 if (!validateCSID(tx_Find.Text))
                    return;
                 query = "SELECT * FROM Accounts WHERE CSID = '" + tx_Find.Text + "';";
                 break;
              case 2:
                 if (!validatePhone(tx_Find.Text))
                    return;
                 query = "SELECT * FROM Accounts WHERE Phone = '" + tx_Find.Text + "';";
                 break;
           }

           getAccount(query);
           Console.WriteLine("Getting account...");
        }

        private bool validateAR(String ar)
        {
           bool valid = true;
           int x;

           if (ar.Length < 6 || ar.Length > 7)
           {
              Console.WriteLine("AR number must be 6 or 7 digits.");
              valid = false;
           }
           else if (!int.TryParse(ar, out x))
           {
              Console.WriteLine("AR number must be a valid number.");
              valid = false;
           }
           else if (x < 900000 || x > 2000000)
           {
              Console.WriteLine("AR number not in valid range.");
              valid = false;
           }

           return valid;
        }

        private bool validateCSID(String csid)
        {
           bool valid = true;
           int x;

           if (csid.Length != 8)
           {
              Console.WriteLine("CSID must be exactly 8 characters long.");
              valid = false;
           }
           else if (char.IsDigit(csid[0]) || char.IsDigit(csid[1]))
           {
              Console.WriteLine("CSID must start with at least 2 letters.");
              valid = false;
           }
           else if (!int.TryParse(csid.Substring(4), out x))
           {
              Console.WriteLine("CSID must end with at least 4 digits.");
              valid = false;
           }

           return valid;
        }

        private bool validatePhone(String phone)
        {
           bool valid = true;

           return valid;
        }

        private void radioButtonReadOnly(object senser, EventArgs e)
        {
           return;
        }
    }
}
